package com.sniff.atp.enums.sql;

import com.sniff.atp.utils.LogUtil;
import lombok.Getter;

import java.util.Arrays;
import java.util.Objects;

@Getter
public enum SniffSql {
    SQL001("Purchase-查询成品采购单号", "select DISTINCT(purchase_no),ware_code,purchase_status from sniff_purchase_order where purchase_id in (\n" +
            "select purchase_order_id from sniff_purchase_order_item_relation where platform_order_no = \"#{}\"\n" +
            ")", "sniff-purchase"),
    SQL002("Purchase-查询OEM采购单号", "select oem_purchase_no, ware_code,purchase_status from sniff_oem_purchase_order where platform_order_no = \"#{}\";", "sniff-purchase"),
    SQL003("WMS-查询组合任务", "select pack_code, warehouse from wms_pack_task where platform_order_no = '#{}'", "sniff-wms"),
    SQL004("Purchase-查询档口市场账户", "select email from sniff_take_user where mall_name like '%#{1}%' and email in (\n" +
            "\t'1211@qq.com', '1234@qq.com'\n" +
            ")", "sniff-customer"),
    SQL007("Purchase-根据采购单查询仓库，物流", "select a.purchase_no, a.logistics_number, b.ware_code, c.ware_code as oem_ware_code from sniff_purchase_order_express as a LEFT JOIN sniff_purchase_order as b on a.purchase_no = b.purchase_no LEFT JOIN sniff_oem_purchase_order as c on a.purchase_no = c.oem_purchase_no \n" +
            "where a.purchase_no = '#{1}'", "sniff-purchase"),
    SQL005("WMS-拣货任务查询", "select * from wms_delivery_pick_task where delivery_task_code like '%#{}%';", "sniff-wms"),
    SQL006("WMS-根据发货单号或平台订单号查寻发货单",
            "select * from wms_delivery_task where (delivery_task_code = '#{}' or platform_order_no = '#{}') and beactive = 1;", "sniff-wms"),
    SQL008("ORDER-根据订单号查寻订单信息",
            "SELECT * FROM `sniff_platform_order` where platform_order_no = '#{}'", "sniff-order"),
    SQL009("WMS-根据订单号查寻发货单订单信息",
            "SELECT * FROM `wms_delivery_review` where platform_order_no = '#{}'", "sniff-wms"),
    SQL010("USER-新用户去掉新手引导",
            "INSERT INTO `sniff_customer`.`sniff_customer_guide` (`customer_id`, `guide_properties`, `create_time`, `update_time`) VALUES ('#{customerId}', '{\\\"guide-pure-all\\\":true,\\\"guide-market-all\\\":true,\\\"guide-channel-all\\\":true,\\\"b2b-order-list\\\":true,\\\"d2c-order-list\\\":true,\\\"b2b-order\\\":true,\\\"d2c-order\\\":true,\\\"create-cssociated\\\":true,\\\"stock-list\\\":true,\\\"b2b-oem-list\\\":true,\\\"d2c-oem-list\\\":true,\\\"b2b-shopcart\\\":true,\\\"d2c-shopcart\\\":true,\\\"cssociated-product\\\":true,\\\"usercenter-b2b\\\":true,\\\"usercenter-d2c\\\":true,\\\"sync-order\\\":true,\\\"create-shipping\\\":true,\\\"combined-goods\\\":true,\\\"shipping-list\\\":true,\\\"replenishment-list\\\":true,\\\"abnormal-order\\\":true,\\\"b2b-recharge\\\":true,\\\"d2c-recharge\\\":true,\\\"d2c-usercenter\\\":true,\\\"d2c-abnormal-order\\\":true,\\\"d2c-steup\\\":true,\\\"d2c-customer-fund\\\":true,\\\"d2c-stock-list\\\":true}', '2022-08-03 10:05:28', '2023-05-12 15:27:05');", "sniff-customer"),
    SQL011("CUSTOMER-根据账号查询客户基本信息",
            "SELECT t1.* FROM sniff_customer t1 left join sniff_customer_shop t2 on t1.customer_id = t2.customer_id where  ( t1.login_name = '#{username}' or t2.customer_shop_id = '#{customerShopId}' or t1.customer_mobile = '#{customerMobile}') and t1.system_source = '#{systemType}' ", "sniff-customer"),
    SQL012("SETTLEMENT-根据客户id更新用户余额",
            "UPDATE `sniff_customer_wallet` SET `available_amount`=500000 WHERE `customer_id`= #{customerId}", "sniff-settlement"),
    SQL013("SETTLEMENT-根据客户店铺id更新用户余额",
            "UPDATE `sniff_customer_wallet` SET `available_amount`=500000 WHERE `customer_shop_id`= #{customerShopId}", "sniff-settlement"),
    SQL014("SETTLEMENT-修改会员等级",
            "UPDATE `sniff_customer_membership` SET `membership_template_id`= #{membershipTemplateId} WHERE `super_customer_id`= #{superCustomerId}", "sniff-customer"),
    SQL015("WMS-根据批次包查询仓库", "SELECT * FROM `wms_pkg` where `pkg_code` = '#{}'", "sniff-wms"),
    SQL016("WMS-根据组合编号查询仓库", "SELECT `warehouse` FROM `sniff_wms`.`wms_pack_task` WHERE `pack_code` = '#{}'", "sniff-wms"),
    SQL017("WMS-根据国际发货单号查询收件人信息", "SELECT * FROM `wms_delivery_task_address` WHERE `delivery_task_code` = '#{}' AND `address_type` IN (0,1)", "sniff-wms"),
    SQL018("获取手机验证码", "SELECT sms_content FROM `sniff_sms_send_record` WHERE `customer_mobile` = '#{}' ORDER BY `id` DESC limit 1", "sniff-customer"),
    SQL019("CUSTOMER-公共传入sql查询", "#{}", "sniff-customer"),
    SQL020("SETTLEMENT-根据客户超级编号更新用户余额",
            "UPDATE `account` SET `available_balance`=10000000 , `balance`=10000000 WHERE `customer_id`= #{customerId}", "sniff_pay"),
    SQL021("WMS-根据采购单查询入库单信息",
            "SELECT * FROM `sniff_wms`.`wms_inbound_order` WHERE `inbound_code` IN (SELECT `inbound_code` FROM `sniff_wms`.`wms_inbound_purchase` WHERE `purchase_no` = '#{}')", "sniff-wms"),
    SQL022("WMS-根据国际发货号查寻审单信息",
            "SELECT * FROM `wms_delivery_review` where `delivery_task_code` = '#{}'", "sniff-wms"),
    SQL023("WMS-根据入库单查询批次包列表", "SELECT * FROM `wms_pkg` where `inbound_code` = '#{}'", "sniff-wms"),
    SQL024("WMS-根据国际发货单查询发货任务物流信息", "SELECT * FROM `wms_delivery_task_logistics_detail` WHERE `delivery_task_code` = '#{}'", "sniff-wms"),
    SQL025("PRODUCT-根据spu查询sku", "SELECT * FROM `sniff_sku` WHERE `product_code` = '#{}'", "sniff_product"),
    SQL026("CUSTOMER-根据客户店铺名称查询用户信息",
            "SELECT * FROM `sniff_customer` t1 left join `sniff_customer_shop` t2 on t1.`customer_id` = t2.`main_customer_id` WHERE t2.`customer_shop_name` = '#{customerShopName}' OR t2.`customer_shop_id` = '#{customerShopId}';", "sniff-customer"),
    SQL027("WMS-根据订单号或国际发货单号查询发货审单表", "SELECT * FROM `wms_delivery_review` WHERE `delivery_task_code` = '#{deliveryTaskCode}' OR `platform_order_no` = '#{platformOrderNo}'", "sniff-wms"),
    SQL028("WMS-根据波次查询拣货任务", "select * from wms_delivery_pick_task where wave_code='#{}';", "sniff-wms"),
    SQL029("WMS-根据国际发货单更新物流单号",
            "UPDATE `wms_delivery_task_logistics_detail` SET `logistics_tag_url`='https://theckbstest.oss-cn-hangzhou.aliyuncs.com/OCSExpressLabelGJFH2312211737668095031541760_222d6120-32f4-450b-8564-21d950561666.pdf',`logistics_code`='#{logisticsCode}' WHERE `delivery_task_code`= '#{deliveryTaskCode}';", "sniff-wms"),
    SQL030("waveWMS-根据国际发货单查询在途未全部抵扣sku", "SELECT * FROM `wms_delivery_task_detail` WHERE `delivery_task_code` = '#{}' AND `beactive` = 1 AND (`in_transit_delivery_nums`-`in_transit_deduction_nums`) > 0;", "sniff-wms"),
    SQL031("WMS-分拣任务查询", "SELECT * FROM `sniff_wms`.`wms_delivery_task_allot_detail` WHERE delivery_task_code LIKE '%#{}%';", "sniff-wms"),
    SQL00001("atp-执行记录",
            "INSERT INTO `atp_test`.`execute_record` (case_name, `req_url`, `req_header`, `req_body`, `resp_code`, `resp_header`, `resp_body`) VALUES ('#{caseName}', '#{reqUrl}', '#{reqHeader}', '#{reqBody}', '#{respCode}', '#{respHeader}', '#{respBody}');", "atp-test"),
    SQL00002("atp-执行结果",
            "INSERT INTO `atp_test`.`execute_result` (`case_name`, `total_count`, `pass_count`, `fail_count`, `skip_count`, `pass_rate`, `start_time`, `end_time`) VALUES ('#{caseName}', #{totalCount}, #{passCount}, #{failCount}, #{skipCount}, #{passRate}, '#{startTime}', '#{endTime}');", "atp-test"),

    SQL_PROD_001("prod-customer-查询用户基本信息",
            "SELECT customer_id, super_customer_id, `unification_customer_full_name`, `login_name`,station_code FROM sniff_customer.`sniff_customer`\n" +
                    "WHERE super_customer_id =( SELECT super_customer_id FROM `sniff_customer` WHERE customer_id = #{customerId} )\n" +
                    "AND account_type = '1'\n" +
                    "AND system_source = '1' limit 1;", "sniff_customer_prod"),
    SQL_PROD_002("prod-customer-查询会员等级信息",
            "SELECT membership_template_id,membership_template_price_id, `price_name` , `discount_actual_price`,`station_code` \n" +
                    "FROM `sniff_settlement`.`sniff_membership_template_price`\n" +
                    "WHERE discount_actual_price = #{membershipFee}\n" +
                    "AND trial_flag = '0' limit 1;", "sniff_customer_prod"),
    SQL_PROD_003("prod-customer-查询指定用户会员等级信息",
            "SELECT membership_template_name as membershipTemplateNameFinal, expires_date as expiresDateFinal\n" +
                    "FROM `sniff_customer_membership`\n" +
                    "WHERE `super_customer_id` = #{superCustomerId} limit 1;", "sniff_customer_prod"),
    SQL_PROD_004("prod-customer-查询会员基本信息",
            "SELECT t1.price_name, t1.`discount_actual_price`, t1.`station_code`, t2.`template_name_zh`, t2.membership_template_id,\n" +
                    "  t1.membership_template_price_id FROM\n" +
                    "  `sniff_settlement`.`sniff_membership_template_price` t1 LEFT JOIN `sniff_settlement`.`sniff_membership_template` t2 on t1.membership_template_id = t2.membership_template_id\n" +
                    "where t1.station_code = '#{customerStationCode}'  and t1.`valid_period` = 30 and t1.`basic_flag` = 1 order by t1.discount_actual_price;", "sniff_customer_prod")
    ;
    // 别名不能重复
    private final String alias;
    private final String sql;
    private final String dbName;

    SniffSql(String alias, String sql, String dbName) {
        this.alias = alias;
        this.sql = sql;
        this.dbName = dbName;
    }

    public static SniffSql getSniffSQL(String alias) {
        return Arrays.stream(SniffSql.values())
                .filter(item -> Objects.equals(item.getAlias(), alias)).findAny()
                .orElse(null);
    }
}
